Using TCL as a simple DDE Client and Excel as the server:
- dde request Excel {[Book1]sheet2}
R1C1 returns the value of the requested cell. Instead of a cell number, a cell name can be used.
- dde poke Excel {[Book1]sheet}
R2C2 "fred" will set the value of the given cell to "fred".
- dde execute excel {&[book1]gary}
{[Select("r1c1:r2c2")]}
Selects a cell or range of cells. Note the curly brackets to 'hide' the square
brackets.
Controlling Excel with DDE
Excel is controlled by using it as a DDE server. Excel is a client only when a cell or range has a formula which uses DDE to get the cell contents. DDE allows you to handle multiple instances of Excel, something which is not possible using automation. Each instance offers the "System" topic. The other topics are of the form "[Workbook]Sheet", one for each open workbook regardless of the Excel instance hosting it. The workbook name is the filename of the opened file, if there is one. The filename without square brackets is also available as a topic.
All DDE Service, Topic and Item names are insensitive to case. The DDE Execute command strings are also case insensitive.
If you request "SysItems" on the System topic, you get a list of items available on that topic:
- SysItems - returns the item list.
- Topics - returns "System" and the list of [Workbook]Sheet topics.
- Status - returns "Ready" or "Busy".
- Formats - returns a tab separated list including XlTable, cvs, rich text format, bitmap, and text. You can request and poke data in any of these formats.
- Selection - returns the current selection of the selected worksheet in the format "[Book&]Sheet!R1C1:R2C2".
- Protocols - returns "StdFileEditing" and "Embedding".
- EditEnvItems - returns a list starting with "StdHostNames".
The format CF_TEXT must be used for all requests on the System topic.
To manipulate single cells or ranges, use DDE Request and Poke commands on the [Workbook]Sheet topics. The DDE item is the cell name (e.g. r1c1) or range (e.g. r1c1:r10c2).
Single cell data is straightforward. To read the data in a cell, issue a request for the data with the item name being the cell address, such as r1c1. You can also start an advise loop for the cell, known as a hot link. Excel sends the cell contents to the client whenever it changes. To modify the data in a cell, issue a poke transaction with the item name being the cell address as in a request.
There are several ways to populate or read a range, when the target is an array of cells.
- In text form, separate the colums with Tab characters and put each row on a new line, to make a grid like the Excel cells. Poke using CF_TEXT. If you request a range in text format the data is laid out in the way described.
- Separate the columns with commas instead, to make a CSV format (comma separated variables). Register the Clipboard format "CSV" and use that instead of CF_TEXT.
- You can also use XLTable for transferring data when the item name is a range.
You cannot poke or request any part of the active selection if it is being edited in Excel.
Built-in macros can be run on any topic with a DDE Execute command. The command string is the macro enclosed in square brackets, for example [Select("r1c1:r2c2")]. Commands can be concatenated, for example [ALERT("Hello world")];[ALERT("Hello again")].
To get help on the macros, download macrofun.exe or macro97.exe from the Microsoft web site from these Knowledgebase articles Q143466 for macro97.exe and Q128185 for macrofun.exe.
To run macros you have written, use the RUN macro like this: [RUN("MyMacro")]. Your macros cannot have parameters. You can write macros to set variables, then execute other macros which examine the variables you have assigned.
In the DDE Software download area there are sample programs (with full source) which manipulate Microsoft Excel using DDE. The compiled C program is only 17kb, and it does not rely on any external DLLs (apart from the Common Controls which is for the User Interface, not DDE).
taken from
http://www.angelfire.com/biz/rhaminisys/ddeapps.html#DDEShell